
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class mysqlConnection {

	public Connection conn;

	public mysqlConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {
		}

		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost/library","root","Braude");
			System.out.println("SQL Connected Successfuly");
			DBCreate("library");
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}
	
	public void DBCreate(String DBname){

		Statement stmt;
		try {
			
			stmt = conn.createStatement();
		      stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS  "+DBname);
		      stmt.close();
		 
		} catch (SQLException e) {	e.printStackTrace();}
		 		
	}

	public void printTable(String tblName) {
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM " +tblName);
			while (rs.next()) {
				System.out.println("Username:"+rs.getString(2)+" Password:" +rs.getString(3)+" Type:" +rs.getString(4));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<String> LogIn(String username, String password) {
		Statement stmt;
		ArrayList<String> res = new ArrayList<>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE username='" + username + "' AND password='" + password + "';");
			if (rs.next()) {
				res.add("Logged in successfuly");
				return res;
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		res.add("Incorrect Login Details");
		return res;
	}

	public ArrayList<String> AddNewUser(String userID, String username, String password, String type) {
		ArrayList<String> res = new ArrayList<>();		
		try {
			if (LogIn(username, password).get(0).equals("Logged in successfuly")) {
				res.add("This User Already Exists");
			} else {
				Statement stmt;
				stmt = conn.createStatement();
				ResultSet rs2 = stmt.executeQuery("SELECT * FROM user WHERE userID='" + userID + "';");
				if (rs2.next()) {
					res.add("UserID already exists");
					return res;
				}
				rs2.close();
				if(userID.length()==9){
				int rs1 = stmt.executeUpdate("INSERT INTO user(userID,username,password,type) VALUES('" + userID + "' ,'" + username + "' ,'" + password + "' , '" + type + "');");
				if (rs1 == 1) {
					res.add("New User Was Created Successfuly");
				} else {
					res.add("Incorrect Details");
				}
				stmt.close();
			}
				else res.add("Incorrect userID");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}
	
	public ArrayList<String> ViewUser(String userID) {
		Statement stmt;
		ArrayList<String> res = new ArrayList<>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE userID='" + userID + "';");
			if (rs.next()) {
				res.add("Username:"+rs.getString(2)+" Password:" +rs.getString(3)+" Type:" +rs.getString(4));
				return res;
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		res.add("No Such User");
		return res;
	}

}